package com.ziyear.dao.impl;

import com.ziyear.common.util.JDBCUtil;
import com.ziyear.dao.PrepDao;
import com.ziyear.domain.Prep;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;


public class PrepDaoImpl implements PrepDao {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    @Override
    public int add(Prep prep) throws SQLException {
        String sql = "insert into prep (trainid,pmemberid,startstation,endstation,trainnumber,starttime,endtime,price," +
                "way,booktime,trainseatid,seatcode,waiting,seattype) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, prep.getTrainId());
        pstmt.setObject(2, prep.getPmemberId());
        pstmt.setObject(3, prep.getStartStation());
        pstmt.setObject(4, prep.getEndStation());
        pstmt.setObject(5, prep.getTrainNumber());
        pstmt.setObject(6, prep.getStartTime());
        pstmt.setObject(7, prep.getEndTime());
        pstmt.setObject(8, prep.getPrice());
        pstmt.setObject(9, prep.isWay());
        pstmt.setObject(10, prep.getBooktime());
        pstmt.setObject(11, prep.getTrainSeatId());
        pstmt.setObject(12, prep.getSeatCode());
        pstmt.setObject(13, prep.isWaiting());
        pstmt.setObject(14, prep.getSeatType());
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public int delete(Integer prepId) throws SQLException {
        String sql = "delete from prep where prepid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, prepId);
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public int update(Prep prep) throws SQLException {
        String sql = "update prep set trainid=?,pmemberid=?,startstation=?,endstation=?,trainnumber=?,starttime=?,endtime=?,price=?,way=?," +
                "trainseatid=?,seatcode=?,waiting=?,seattype=? where prepid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, prep.getTrainId());
        pstmt.setObject(2, prep.getPmemberId());
        pstmt.setObject(3, prep.getStartStation());
        pstmt.setObject(4, prep.getEndStation());
        pstmt.setObject(5, prep.getTrainNumber());
        pstmt.setObject(6, prep.getStartTime());
        pstmt.setObject(7, prep.getEndTime());
        pstmt.setObject(8, prep.getPrice());
        pstmt.setObject(9, prep.isWay());
        pstmt.setObject(10, prep.getTrainSeatId());
        pstmt.setObject(11, prep.getSeatCode());
        pstmt.setObject(12, prep.isWaiting());
        pstmt.setObject(13, prep.getSeatType());
        pstmt.setObject(14, prep.getPrepId());
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public List<Prep> getAll() throws SQLException {
        String sql = "select * from prep";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        List<Prep> list = new ArrayList<Prep>();
        while (rs.next()) {
            int prepId = rs.getInt("prepId");
            int trainId = rs.getInt("trainId");
            int pmemberId = rs.getInt("pmemberId");
            String startStation = rs.getString("startStation");
            String endStation = rs.getString("endStation");
            String trainNumber = rs.getString("trainNumber");
            String startTime = rs.getString("startTime");
            String endTime = rs.getString("endTime");
            BigDecimal price = rs.getBigDecimal("price");
            boolean way = rs.getBoolean("way");
            Timestamp booktime = rs.getTimestamp("booktime");
            int trainseatid = rs.getInt("trainseatid");
            String seatcode = rs.getString("seatcode");
            boolean waiting = rs.getBoolean("waiting");
            String seattype = rs.getString("seattype");
            Prep prep = new Prep(prepId, trainId, pmemberId, startStation, endStation, trainNumber, startTime,
                    endTime, price, way, booktime, trainseatid, seatcode, waiting, seattype);
            list.add(prep);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public List<Prep> getPrepByPmemberId(Integer pmemberId) throws SQLException {
        String sql = "select * from prep where pmemberid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, pmemberId);
        rs = pstmt.executeQuery();
        List<Prep> list = new ArrayList<Prep>();
        while (rs.next()) {
            int prepId = rs.getInt("prepId");
            int trainId = rs.getInt("trainId");
            String startStation = rs.getString("startStation");
            String endStation = rs.getString("endStation");
            String trainNumber = rs.getString("trainNumber");
            String startTime = rs.getString("startTime");
            String endTime = rs.getString("endTime");
            BigDecimal price = rs.getBigDecimal("price");
            boolean way = rs.getBoolean("way");
            Timestamp booktime = rs.getTimestamp("booktime");
            int trainseatid = rs.getInt("trainseatid");
            String seatcode = rs.getString("seatcode");
            boolean waiting = rs.getBoolean("waiting");
            String seattype = rs.getString("seattype");
            Prep prep = new Prep(prepId, trainId, pmemberId, startStation, endStation, trainNumber,
                    startTime, endTime, price, way, booktime, trainseatid, seatcode, waiting, seattype);
            list.add(prep);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public Prep getById(Integer prepId) throws SQLException {
        String sql = "select * from prep where prepid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, prepId);
        rs = pstmt.executeQuery();
        Prep prep = null;
        while (rs.next()) {
            int trainId = rs.getInt("trainId");
            int pmemberId = rs.getInt("pmemberId");
            String startStation = rs.getString("startStation");
            String endStation = rs.getString("endStation");
            String trainNumber = rs.getString("trainNumber");
            String startTime = rs.getString("startTime");
            String endTime = rs.getString("endTime");
            BigDecimal price = rs.getBigDecimal("price");
            boolean way = rs.getBoolean("way");
            Timestamp booktime = rs.getTimestamp("booktime");
            int trainseatid = rs.getInt("trainseatid");
            String seatcode = rs.getString("seatcode");
            boolean waiting = rs.getBoolean("waiting");
            String seattype = rs.getString("seattype");
            prep = new Prep(prepId, trainId, pmemberId, startStation, endStation, trainNumber, startTime,
                    endTime, price, way, booktime, trainseatid, seatcode, waiting, seattype);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return prep;
    }

    @Override
    public Prep queryEarlyWaitingPrep(Integer trainId, String seatType) throws SQLException {
        String sql = "select * from prep where trainId=? and seattype=? and waiting=? order by booktime limit 1";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        pstmt.setObject(2, seatType);
        pstmt.setObject(3, true);
        rs = pstmt.executeQuery();
        Prep prep = null;
        while (rs.next()) {
            int prepid = rs.getInt("prepid");
            int trainid = rs.getInt("trainid");
            int pmemberId = rs.getInt("pmemberid");
            String startStation = rs.getString("startstation");
            String endStation = rs.getString("endstation");
            String trainNumber = rs.getString("trainnumber");
            String startTime = rs.getString("starttime");
            String endTime = rs.getString("endtime");
            BigDecimal price = rs.getBigDecimal("price");
            boolean way = rs.getBoolean("way");
            Timestamp booktime = rs.getTimestamp("booktime");
            int trainseatid = rs.getInt("trainseatid");
            String seatcode = rs.getString("seatcode");
            boolean waiting = rs.getBoolean("waiting");
            String seattype = rs.getString("seattype");
            prep = new Prep(prepid, trainid, pmemberId, startStation, endStation, trainNumber, startTime, endTime,
                    price, way, booktime, trainseatid, seatcode, waiting, seattype);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return prep;
    }

    @Override
    public Prep getPrepByTrainIdAndMemberId(Integer trainId, Integer memberId) throws SQLException {
        String sql = "select * from prep where trainId=? and pmemberid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        pstmt.setObject(2, memberId);
        rs = pstmt.executeQuery();
        Prep prep = null;
        while (rs.next()) {
            int prepid = rs.getInt("prepid");
            int trainid = rs.getInt("trainid");
            int pmemberId = rs.getInt("pmemberid");
            String startStation = rs.getString("startstation");
            String endStation = rs.getString("endstation");
            String trainNumber = rs.getString("trainnumber");
            String startTime = rs.getString("starttime");
            String endTime = rs.getString("endtime");
            BigDecimal price = rs.getBigDecimal("price");
            boolean way = rs.getBoolean("way");
            Timestamp booktime = rs.getTimestamp("booktime");
            int trainseatid = rs.getInt("trainseatid");
            String seatcode = rs.getString("seatcode");
            boolean waiting = rs.getBoolean("waiting");
            String seattype = rs.getString("seattype");
            prep = new Prep(prepid, trainid, pmemberId, startStation, endStation, trainNumber, startTime, endTime,
                    price, way, booktime, trainseatid, seatcode, waiting, seattype);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return prep;
    }

    @Override
    public List<Prep> getPrepByTrainId(Integer trainId) throws SQLException {
        String sql = "select * from prep where trainId=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        rs = pstmt.executeQuery();
        List<Prep> result = new ArrayList<>();
        while (rs.next()) {
            int prepid = rs.getInt("prepid");
            int trainid = rs.getInt("trainid");
            int pmemberId = rs.getInt("pmemberid");
            String startStation = rs.getString("startstation");
            String endStation = rs.getString("endstation");
            String trainNumber = rs.getString("trainnumber");
            String startTime = rs.getString("starttime");
            String endTime = rs.getString("endtime");
            BigDecimal price = rs.getBigDecimal("price");
            boolean way = rs.getBoolean("way");
            Timestamp booktime = rs.getTimestamp("booktime");
            int trainseatid = rs.getInt("trainseatid");
            String seatcode = rs.getString("seatcode");
            boolean waiting = rs.getBoolean("waiting");
            String seattype = rs.getString("seattype");
            Prep prep = new Prep(prepid, trainid, pmemberId, startStation, endStation, trainNumber, startTime, endTime,
                    price, way, booktime, trainseatid, seatcode, waiting, seattype);
            result.add(prep);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return result;
    }

}
